﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using Common.Utilities;
using Ionic.Utils.Zip;

namespace Common.Utilities.OpenXml
{
    /// <summary>
    /// 
    /// </summary>
    public class ExcelWriter
    {
        /// <summary>
        /// create excel (.xlsx) file containing tables contained in data set
        /// one table for each worksheet, worksheet takes the name it binds
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="ds"></param>
        public static void CreateWorkbook(string filePath, DataSet ds)
        {
            List<string> sheetNames = new List<string>();
            foreach (DataTable dt in ds.Tables)
            {
                sheetNames.Add(dt.TableName);
            }
            CreateBasePackage(filePath, sheetNames);
            FileStream fs = File.OpenRead(filePath);
            byte[] contents = new byte[fs.Length];
            fs.Read(contents, 0, (int)fs.Length);
            fs.Close();

            // open the package from the resource
            using (PackageHelper package = new PackageHelper(contents))
            {
                SharedStrings sharedStrings = new SharedStrings(package);

                // int tableID = 0;
                foreach (DataTable dt in ds.Tables)
                {
                    //tableID++;
                    // load the sheet package part into an XmlDocument
                    Uri sheetUri =
                        new Uri(string.Format(@"/xl/worksheets/{0}.xml", dt.TableName), UriKind.Relative);

                    XmlDocument sheetXml = package.GetWritablePart(sheetUri);
                    if(sheetXml ==null)
                    {
                        package.CreateNewPart(
                            sheetUri,
                            @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml",
                            Encoding.Default.GetBytes(Properties.Resources.sheet1));
                        sheetXml = package.GetWritablePart(sheetUri);
                    }

                    XPathNavigator sheetData = sheetXml.CreateNavigator()
                        .SelectSingleNode("x:worksheet/x:sheetData",
                            Namespaces.NamespaceManager);

                    using (XmlWriter writer = sheetData.AppendChild())
                    {
                        // create the instance of the sales report document
                        GridHelper gridHelper = new GridHelper(dt, sharedStrings);

                        // write the document to the XmlWriter
                        gridHelper.WriteTo(writer);
                    }

                    //// add a table over the sales report grid
                    //CreateTable(package, sheetUri, sheetXml, dt, tableID);

                    //// write the document into the package part
                    package.SavePart(sheetUri, sheetXml);

                    /*
                    // create the pivot table definition
                    PivotCacheDefinition pivotCacheDefinition =
                        PivotCacheDefinition.Create(package,
                            "pivotCacheDefinition1",
                            Properties.Resources.
                                SalesByTerritoryPivotCacheDefinition);

                    // add the pivot cache definition to the workbook
                    Workbook workbook = Workbook.Open(package);
                    int cacheId =
                        workbook.AddPivotCacheDefinition(pivotCacheDefinition);
                    workbook.Flush();

                    // create the pivot table
                    PivotTable pivotTable = PivotTable.Create(package,
                        "pivotTable",
                        Properties.Resources.SalesByTerritoryPivotTable,
                        pivotCacheDefinition);
                    pivotTable.SetDefinitionCacheId(cacheId);
                    pivotTable.Flush();
                    */
                }

                // save the shared strings
                sharedStrings.Save(package);

                // save package 
                package.Save(filePath);
            }
        }

        /// <summary>
        /// docProps\app.xml
        /// docProps\core.xml
        /// xl\styles.xml
        /// theme\theme1.xml
        /// xl\workbook.xml
        /// worksheets\sheetname1.xml
        /// worksheets\sheetname2.xml
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetNames"></param>
        /// <returns></returns>
        private static void CreateBasePackage(string filePath, List<string> sheetNames)
        {
            string packageFolderPath = Path.GetTempPath();
            string tmpPkgName = Path.GetFileNameWithoutExtension(Path.GetTempFileName());
            packageFolderPath = Path.Combine(packageFolderPath, tmpPkgName);
            if (Directory.Exists(packageFolderPath))
            {
                Directory.SetCurrentDirectory(Path.GetTempPath());
                Directory.Delete(packageFolderPath, true);
            }
            Directory.CreateDirectory(packageFolderPath);
            // root 
            XmlDocument contentType = new XmlDocument();
            contentType.LoadXml(Properties.Resources._Content_Types_);
            contentType.Save(Path.Combine(packageFolderPath, "[Content_Types].xml"));

            // _rels
            string relsPath = Path.Combine(packageFolderPath, "_rels");
            Directory.CreateDirectory(relsPath);
            XmlDocument rels = new XmlDocument();
            rels.LoadXml(Properties.Resources._);
            rels.Save(Path.Combine(relsPath, ".rels"));
            // docProps
            string docPropsPath = Path.Combine(packageFolderPath, "docProps");
            Directory.CreateDirectory(docPropsPath);
            XmlDocument app = new XmlDocument();
            app.LoadXml(Properties.Resources.app);

            XmlNode headingPairRootNode = null;
            foreach (XmlNode xnode in app.DocumentElement.ChildNodes)
            {
                if (xnode.Name == "HeadingPairs")
                {
                    headingPairRootNode = xnode;
                    break;
                }
            }
            if(headingPairRootNode !=null && headingPairRootNode.ChildNodes !=null)
            {
                foreach(XmlNode vtNode in headingPairRootNode.ChildNodes)
                {
                    if (vtNode.Name == "vt:vector")
                    {
                        vtNode.RemoveAll();
                        XmlDataUtil.UpdateAttribute(ref app, vtNode, "size", "2");
                        XmlDataUtil.UpdateAttribute(ref app, vtNode, "baseType", "variant");

                        XmlNode variantNode1 = XmlDataUtil.AddElementWithPrefix(ref app, vtNode, Prefixes.VTType, Namespaces.DocPropVTType, "variant");
                        XmlNode lpstrNode1 = XmlDataUtil.AddElementWithPrefix(ref app, variantNode1, Prefixes.VTType, Namespaces.DocPropVTType, "lpstr");
                        lpstrNode1.InnerText = "Worksheets";
                        XmlNode variantNode2 = XmlDataUtil.AddElementWithPrefix(ref app, vtNode, Prefixes.VTType, Namespaces.DocPropVTType, "variant");
                        XmlNode lpstrNode2 = XmlDataUtil.AddElementWithPrefix(ref app, variantNode2, Prefixes.VTType, Namespaces.DocPropVTType, "i4");
                        lpstrNode2.InnerText = sheetNames.Count.ToString();
                
                        break;
                    }
                }
            }

            XmlNode partRootNode = null;
            foreach (XmlNode xnode in app.DocumentElement.ChildNodes)
            {
                if (xnode.Name == "TitlesOfParts")
                {
                    partRootNode = xnode;
                    break;
                }
            }
            if (partRootNode != null)
            {
                partRootNode.RemoveAll();
                XmlNode vecNode =
                    XmlDataUtil.AddElementWithPrefix(ref app, partRootNode, Prefixes.VTType, Namespaces.DocPropVTType, "vector");

                XmlDataUtil.UpdateAttribute(ref app, vecNode, "size", sheetNames.Count.ToString());
                XmlDataUtil.UpdateAttribute(ref app, vecNode, "baseType", "lpstr");
                foreach (string sheetName in sheetNames)
                {
                    XmlNode lpstrNode = XmlDataUtil.AddElementWithPrefix(ref app, vecNode, Prefixes.VTType, Namespaces.DocPropVTType, "lpstr");
                    lpstrNode.InnerText = sheetName;
                }
            }
            string appXml = app.OuterXml.Replace(@"xmlns=""""", "");
            app = new XmlDocument();
            app.LoadXml(appXml);
            app.Save(Path.Combine(packageFolderPath, Path.Combine(docPropsPath, "app.xml")));
            XmlDocument core = new XmlDocument();
            core.LoadXml(Properties.Resources.core);
            core.Save(Path.Combine(docPropsPath, "core.xml"));

            // xl
            string xlPath = Path.Combine(packageFolderPath, "xl");
            Directory.CreateDirectory(xlPath);
            XmlDocument styles = new XmlDocument();
            styles.LoadXml(Properties.Resources.styles);
            styles.Save(Path.Combine(xlPath, "styles.xml"));
            XmlDocument workbook = new XmlDocument();
            workbook.LoadXml(Properties.Resources.workbook);
            XmlNode sheetRootNode = null;
            foreach (XmlNode xnode in workbook.DocumentElement.ChildNodes)
            {
                if (xnode.Name == "sheets")
                {
                    sheetRootNode = xnode;
                    break;
                }
            }
            int sheetIndex = 0;
            if (sheetRootNode != null)
            {
                foreach (string sheetName in sheetNames)
                {
                    sheetIndex++;
                    XmlNode sheetNode = XmlDataUtil.AddElement(ref workbook, sheetRootNode, "sheet");
                    XmlDataUtil.UpdateAttribute(ref workbook, sheetNode, "name", sheetName);
                    XmlDataUtil.UpdateAttribute(ref workbook, sheetNode, "sheetId", sheetIndex.ToString());
                    XmlDataUtil.UpdateAttribute(ref workbook, sheetNode, Prefixes.Relationships, Namespaces.Relationships, "id",
                                                string.Format("rId{0}", sheetIndex));
                }
            }
            // bug fix: 
            string workbookXml = workbook.OuterXml.Replace(@"xmlns=""""", "");
            workbook = new XmlDocument();
            workbook.LoadXml(workbookXml);
            workbook.Save(Path.Combine(xlPath, "workbook.xml"));
            // xl/_rels
            string xlrelsPath = Path.Combine(xlPath, "_rels");
            Directory.CreateDirectory(xlrelsPath);
            XmlDocument xlrels = new XmlDocument();
            xlrels.LoadXml(Properties.Resources.workbook_xml);
            XmlNode workbookRootNode = xlrels.DocumentElement;
            workbookRootNode.RemoveAll();
            XmlDataUtil.UpdateAttribute(ref xlrels, workbookRootNode, "xmlns", "http://schemas.openxmlformats.org/package/2006/relationships");
            int idx = 0;
            foreach (string sheetName in sheetNames)
            {
                idx++;
                XmlNode relNode = XmlDataUtil.AddElement(ref xlrels, workbookRootNode, "Relationship");
                XmlDataUtil.UpdateAttribute(ref xlrels, relNode, "Id", "rId" + idx.ToString());
                XmlDataUtil.UpdateAttribute(ref xlrels, relNode, "Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
                XmlDataUtil.UpdateAttribute(ref xlrels, relNode, "Target", "worksheets/" + sheetName + ".xml");
            }
            idx++;
            XmlNode relStyleNode = XmlDataUtil.AddElement(ref xlrels, workbookRootNode, "Relationship");
            XmlDataUtil.UpdateAttribute(ref xlrels, relStyleNode, "Id", "rId" + idx.ToString());
            XmlDataUtil.UpdateAttribute(ref xlrels, relStyleNode, "Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles");
            XmlDataUtil.UpdateAttribute(ref xlrels, relStyleNode, "Target", "styles.xml");
            idx++;
            XmlNode relThemeNode = XmlDataUtil.AddElement(ref xlrels, workbookRootNode, "Relationship");
            XmlDataUtil.UpdateAttribute(ref xlrels, relThemeNode, "Id", "rId" + idx.ToString());
            XmlDataUtil.UpdateAttribute(ref xlrels, relThemeNode, "Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme");
            XmlDataUtil.UpdateAttribute(ref xlrels, relThemeNode, "Target", "theme/theme1.xml");
            string xlresXML = xlrels.OuterXml.Replace(@"xmlns=""""", "");
            xlrels = new XmlDocument();
            xlrels.LoadXml(xlresXML);
            xlrels.Save(Path.Combine(xlrelsPath, "workbook.xml.rels"));
            // xl/theme
            string themePath = Path.Combine(xlPath, "theme");
            Directory.CreateDirectory(themePath);
            XmlDocument theme = new XmlDocument();
            theme.LoadXml(Properties.Resources.theme1);
            theme.Save(Path.Combine(themePath, "theme1.xml"));
            // xl/worksheets
            string worksheetPath = Path.Combine(xlPath, "worksheets");
            Directory.CreateDirectory(worksheetPath);
            sheetIndex = 0;
            foreach (string sheetName in sheetNames)
            {
                sheetIndex++;
                XmlDocument sheet = new XmlDocument();
                sheet.LoadXml(Properties.Resources.sheet1);
                if (sheetIndex == 1)
                {
                    XmlNode sheetViewNode = null;
                    foreach (XmlNode xnode in sheet.DocumentElement.ChildNodes)
                    {
                        if (xnode.Name == "sheetViews")
                        {
                            sheetViewNode = xnode.ChildNodes[0];
                            break;
                        }
                    }
                    if (sheetViewNode != null)
                    {
                        XmlDataUtil.UpdateAttribute(ref sheet, sheetViewNode, "tabSelected", "1");
                    }
                }
                sheet.Save(Path.Combine(worksheetPath, sheetName + ".xml"));
            }


            ZipFile zip = new ZipFile();
            Directory.SetCurrentDirectory(packageFolderPath);
            zip.AddFile("[Content_Types].xml");
            zip.AddDirectory("_rels");
            zip.AddDirectory("docProps");
            zip.AddDirectory("xl");

            zip.Save(filePath);
            zip.Dispose();
            zip = null;
            GC.Collect();
            GC.WaitForFullGCComplete();

            if (Directory.Exists(packageFolderPath))
            {
                Directory.SetCurrentDirectory(Path.GetTempPath());
                Directory.Delete(packageFolderPath, true);
            }
        }

        #region table
        /// <summary>
        /// Creates a table containing the sales report data.
        /// </summary>
        /// <param name="package">The current package being used.</param>
        /// <param name="sheet1Uri">The uri of the sheet to use.</param>
        /// <param name="sheetXml">The xml document containing the xml in the package part.</param>
        /// <param name="dt">simple flat table (no format, formular).</param>
        /// <param name="tableID">1,2,3</param>
        private static void CreateTable(
            PackageHelper package, Uri sheet1Uri, XmlDocument sheetXml,
            DataTable dt, int tableID)
        {
            XmlDocument tableDoc = CreateTableTemplate(dt, tableID);

            // add a table over the existing data
            XPathNavigator sheet = sheetXml.CreateNavigator()
                .SelectSingleNode("x:worksheet",
                    Namespaces.NamespaceManager);
            using (XmlWriter writer = sheet.AppendChild())
            {
                // create the table package part
                Uri tableUri =
                    new Uri(string.Format("/xl/tables/table{0}.xml", tableID), UriKind.Relative);
                package.CreateNewPart(tableUri,
                                      "application/vnd.openxmlformats-" +
                                      "officedocument.spreadsheetml.table+xml",
                                      Encoding.Default.GetBytes(tableDoc.OuterXml));

                // calculate the total number of rows that will be used
                int dataRowCount = dt.Rows.Count;

                // update the reference range
                XmlDocument tableXml = package.GetWritablePart(tableUri);
                XmlNode tableRef = tableXml.SelectSingleNode("x:table/@ref", Namespaces.NamespaceManager);
                string lastColumnPos = ExcelRangeHelper.GetColumnName(dt.Columns.Count);
                tableRef.Value = string.Format("A1:{0}{1}", lastColumnPos, dataRowCount + 1);
                package.SavePart(tableUri, tableXml);

                // relate the table part to the sheet part
                string tableRelId =
                    package.CreateInternalRelationship(
                        sheet1Uri, tableUri,
                        "http://schemas.openxmlformats.org/officeDocument/" +
                            "2006/relationships/table");

                // write the table to the sheet
                // <tableParts count="1">
                //   <tablePart r:id="rId2" />
                // </tableParts>
                writer.WriteStartElement(Prefixes.SpreadsheetML,
                    "tableParts", Namespaces.SpreadsheetML);
                writer.WriteAttributeString("count", "1");
                writer.WriteStartElement(Prefixes.SpreadsheetML,
                    "tablePart", Namespaces.SpreadsheetML);
                writer.WriteAttributeString(Prefixes.Relationships,
                    "id", Namespaces.Relationships, tableRelId);
                writer.WriteEndElement();
                writer.WriteEndElement();
            }
        }

        private static XmlDocument CreateTableTemplate(DataTable dt, int tableID)
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml("<table></table>");
            string lastColumnPos = ExcelRangeHelper.GetColumnName(dt.Columns.Count);
            int lastRowNum = dt.Rows.Count + 1;
            XmlNode root = xDoc.DocumentElement;
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "id", tableID.ToString());
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "name", dt.TableName);
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "displayName", dt.TableName);
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "ref",
                                        string.Format("{0}:{1}", "A1", lastColumnPos + lastRowNum.ToString()));
            XmlDataUtil.UpdateAttribute(ref xDoc, root, "totalsRowShown", "0");
            XmlNode filterNode = XmlDataUtil.AddElement(ref xDoc, root, "autoFilter");
            XmlDataUtil.UpdateAttribute(ref xDoc, filterNode, "ref",
                                        string.Format("{0}:{1}", "A1", lastColumnPos + lastRowNum.ToString()));
            XmlNode columnRootNode = XmlDataUtil.AddElement(ref xDoc, root, "tableColumns");
            XmlDataUtil.UpdateAttribute(ref xDoc, columnRootNode, "count", dt.Columns.Count.ToString());
            int colOrdinal = 0;
            foreach (DataColumn col in dt.Columns)
            {
                colOrdinal++;
                XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, columnRootNode, "tableColumn");
                XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "id", colOrdinal.ToString());
                XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "name", col.ColumnName);
            }
            XmlNode styleNode = XmlDataUtil.AddElement(ref xDoc, root, "tableStyleInfo");
            XmlDataUtil.UpdateAttribute(ref xDoc, styleNode, "name", "TableStyleMedium9");
            XmlDataUtil.UpdateAttribute(ref xDoc, styleNode, "showFirstColumn", "0");
            XmlDataUtil.UpdateAttribute(ref xDoc, styleNode, "showLastColumn", "0");
            XmlDataUtil.UpdateAttribute(ref xDoc, styleNode, "showRowStripes", "1");
            XmlDataUtil.UpdateAttribute(ref xDoc, styleNode, "showColumnStripes", "0");

            return xDoc;
        }

        /// <summary>
        /// this method format table and create drop down list for column and auto filter part
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        /// <param name="tableID"></param>
        public static void FormatWorksheetTable(string filePath, string sheetName, DataTable dt, int tableID)
        {
            FileStream fs = File.OpenRead(filePath);
            byte[] contents = new byte[fs.Length];
            fs.Read(contents, 0, (int)fs.Length);
            fs.Close();
            using (PackageHelper package = new PackageHelper(contents))
            {
                Uri sheetUri =
                        new Uri(string.Format(@"/xl/worksheets/{0}.xml", dt.TableName), UriKind.Relative);
                XmlDocument sheetXml = package.GetWritablePart(sheetUri);
                // add a table over the sales report grid
                CreateTable(package, sheetUri, sheetXml, dt, tableID);

                // write the document into the package part
                package.SavePart(sheetUri, sheetXml);

                package.Save(filePath);
            }
        }
        #endregion

        #region chart
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dsSheetData"></param>
        /// <param name="drawingID"></param>
        /// <param name="chartDef"></param>
        public static void CreateChart(string filePath, DataSet dsSheetData, int drawingID, ChartDefinition chartDef)
        {
            PackageHelper package = null;
            try
            {
                if (File.Exists(filePath))
                {
                    FileStream fs = File.OpenRead(filePath);
                    byte[] contents = new byte[fs.Length];
                    fs.Read(contents, 0, (int)fs.Length);
                    fs.Close();
                    package = new PackageHelper(contents);
                }
                else
                {
                    package = new PackageHelper(Properties.Resources.BaseSpreadsheetML);
                }

                // create the chart
                Chart chart =
                    Chart.Create(package, drawingID, chartDef, dsSheetData.Tables[chartDef.ChartDataTable]);

                // add the entities to the worksheet
                Worksheet chartSheet = Worksheet.Open(package, chartDef.SheetName);
                // chartSheet.AddPivotTable(pivotTable);
                chartSheet.AddChart(chart);
                chartSheet.AddConditionalFormatting("B", 2, 11);
                chartSheet.Flush();

                // save package 
                package.Save(filePath);
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to create chart: " + ex.ToString());
            }
            finally
            {
                if (package != null)
                    package.Dispose();
            }
        }
        #endregion
    }
}
